/**
 * <p>
 * Title: ExcelUtil2
 * </p>
 * <p>
 * Descriptions:
 * </p>
 * <p>
 * Company:星海
 * </p>
 * 
 * @author 陈刚
 * @date 2017-4-5 上午10:01:11
 */

package com.cg.javafxdemospring.util;

import net.sf.json.JSONObject;
import org.apache.commons.lang.time.DateFormatUtils;
import org.apache.commons.lang3.StringUtils;
import org.apache.poi.hssf.usermodel.*;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.xssf.usermodel.XSSFCell;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

import java.io.*;
import java.lang.reflect.Field;
import java.lang.reflect.InvocationTargetException;
import java.lang.reflect.Method;
import java.text.DecimalFormat;
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.*;
import java.util.Map.Entry;
import java.util.concurrent.ConcurrentHashMap;
import java.util.stream.Collectors;
import java.util.stream.Stream;

/**
 * <p>
 * Title: ExcelUtil2
 * </p>
 * <p>
 * Descriptions:
 * </p>
 * <p>
 * Company:星海
 * </p>
 * 
 * @author 陈刚
 * @date 2017-4-5 上午10:01:11
 */

public class ExcelUtil {
	public static SimpleDateFormat sdf = new SimpleDateFormat("yyyy/MM/dd HH:mm:ss");
	public static final String POINT = ".";

	// 定义一系列样式
	public static final String style_style = "style_style";// style样式
	public static final String style_font = "style_font";// font样式
	public static final int style_alignment_center = 1;// 水平居中
	public static final int style_vertical_center = 2;// 垂直居中
	public static final int font_bold = 1;// 加粗

	public static <T> List<T> readXlsx(File file, Class<T> clazz) throws NoSuchMethodException, SecurityException,
			IllegalAccessException, InvocationTargetException, InstantiationException {

		System.out.println("readXlsx");
		XSSFWorkbook xssfWorkbook = null;
		try {
			xssfWorkbook = new XSSFWorkbook(new FileInputStream(file));
		} catch (FileNotFoundException e1) {
			e1.printStackTrace();
		} catch (IOException e1) {
			e1.printStackTrace();
		}
		List<T> listT = new ArrayList<T>();
		// 循环工作表Sheet
		for (int numSheet = 0; numSheet < xssfWorkbook.getNumberOfSheets(); numSheet++) {
			XSSFSheet xssfSheet = xssfWorkbook.getSheetAt(numSheet);
			if (xssfSheet == null) {
				continue;
			}
			initSheetMerged(xssfSheet);
			// 循环行Row
			for (int rowNum = 0; rowNum <= xssfSheet.getLastRowNum(); rowNum++) {
				XSSFRow hssfRow = xssfSheet.getRow(rowNum);
				if (hssfRow != null) {
					T t = clazz.newInstance();
					Field[] fields = clazz.getDeclaredFields();
					for (int i = 0; i < fields.length; i++) {
						XSSFCell xssfCell = hssfRow.getCell(i);
						String methodName = "set" + fields[i].getName().substring(0, 1).toUpperCase()
								+ fields[i].getName().substring(1, fields[i].getName().length());
						Method method = clazz.getDeclaredMethod(methodName, fields[i].getType());
						// method.invoke(t, getValue(hssfCell));
						Class<?> fieldType = fields[i].getType();
						String fieldValue = getValue(xssfCell);
						if (fieldType != null) {
							try {
								if (String.class.equals(fieldType)) {
									method.invoke(t, fieldValue);
								} else if (byte.class.equals(fieldType)) {
									method.invoke(t, Byte.parseByte(fieldValue));

								} else if (Byte.class.equals(fieldType)) {
									method.invoke(t, Byte.valueOf(fieldValue));

								} else if (boolean.class.equals(fieldType)) {
									method.invoke(t, Boolean.parseBoolean(fieldValue));

								} else if (Boolean.class.equals(fieldType)) {
									method.invoke(t, Boolean.valueOf(fieldValue));

								} else if (short.class.equals(fieldType)) {
									method.invoke(t, Short.parseShort(fieldValue));

								} else if (Short.class.equals(fieldType)) {
									method.invoke(t, Short.valueOf(fieldValue));

								} else if (int.class.equals(fieldType)) {
									method.invoke(t, Integer.parseInt(fieldValue));

								} else if (Integer.class.equals(fieldType)) {
									method.invoke(t, Integer.valueOf(fieldValue));

								} else if (long.class.equals(fieldType)) {
									method.invoke(t, Long.parseLong(fieldValue));

								} else if (Long.class.equals(fieldType)) {
									method.invoke(t, Long.valueOf(fieldValue));

								} else if (float.class.equals(fieldType)) {
									method.invoke(t, Float.parseFloat(fieldValue));

								} else if (Float.class.equals(fieldType)) {
									method.invoke(t, Float.valueOf(fieldValue));

								} else if (double.class.equals(fieldType)) {
									method.invoke(t, Double.parseDouble(fieldValue));

								} else if (Double.class.equals(fieldType)) {
									method.invoke(t, Double.valueOf(fieldValue));

								} else if (Date.class.equals(fieldType)) {
									SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss.SSS");
									method.invoke(t, sdf.parse(fieldValue));
								}
							} catch (IllegalArgumentException e) {
								e.printStackTrace();
							} catch (ParseException e) {
								e.printStackTrace();
							}

						}

					}
					listT.add(t);
				}
			}
		}
		return listT;
	}

	@SuppressWarnings({ "static-access", "deprecation" })
	private static String getValue(HSSFCell hssfCell) {
		if (hssfCell == null) {
			return "";
		}
		if (hssfCell.getCellType() == Cell.CELL_TYPE_BOOLEAN) {
			// 返回布尔类型的值
			return String.valueOf(hssfCell.getBooleanCellValue());
		} else if (hssfCell.getCellType() == Cell.CELL_TYPE_NUMERIC) {
			if (HSSFDateUtil.isCellDateFormatted(hssfCell)) {
				// 如果是时间类型
				return DateFormatUtils.format(hssfCell.getDateCellValue(), "yyyy-MM-dd");
			} else {
				// 返回数值类型的值
				return String.valueOf(((Double) hssfCell.getNumericCellValue()).doubleValue());
			}
		} else {
			// 返回字符串类型的值

			hssfCell.setCellType(Cell.CELL_TYPE_STRING);
			return String.valueOf(hssfCell.getStringCellValue());
		}
	}
	private static void initSheetMerged(HSSFSheet sheet) {
		List<CellRangeAddress> merges = sheet.getMergedRegions();
		for(CellRangeAddress cellRange: merges) {
			int firstColumn = cellRange.getFirstColumn();
			int firstRow = cellRange.getFirstRow();
			int lastColumn = cellRange.getLastColumn();
			int lastRow = cellRange.getLastRow();
			String value=null;
			for(int r=firstRow;r<=lastRow;r++) {
				HSSFRow row = sheet.getRow(r);
				for(int c=firstColumn;c<=lastColumn;c++) {
					HSSFCell cell = row.getCell(c);
					cell.setCellType(CellType.STRING);
					if(value==null){
						value=cell.getStringCellValue();
					}else {
						cell.setCellValue(value);
					}
				}
			}
		}
	}
	private static void initSheetMerged(XSSFSheet sheet) {
		List<CellRangeAddress> merges = sheet.getMergedRegions();
		for(CellRangeAddress cellRange: merges) {
			int firstColumn = cellRange.getFirstColumn();
			int firstRow = cellRange.getFirstRow();
			int lastColumn = cellRange.getLastColumn();
			int lastRow = cellRange.getLastRow();
			String value=null;
			for(int r=firstRow;r<=lastRow;r++) {
				XSSFRow row = sheet.getRow(r);
				for(int c=firstColumn;c<=lastColumn;c++) {
					XSSFCell cell = row.getCell(c);
					cell.setCellType(CellType.STRING);
					if(value==null){
						value=cell.getStringCellValue();
					}else {
						cell.setCellValue(value);
					}
				}
			}
		}
	}
	/**
	 * 单元格格式
	 * 
	 * @param xssfCell
	 * @return
	 */
	@SuppressWarnings("deprecation")
	private static String getValue(XSSFCell xssfCell) {
		if (xssfCell == null) {
            return "";
        }
		if (xssfCell.getCellType() == Cell.CELL_TYPE_BOOLEAN) {
			return String.valueOf(xssfCell.getBooleanCellValue());
		} else if (xssfCell.getCellType() == Cell.CELL_TYPE_NUMERIC) {
			String cellValue = "";
			if (org.apache.poi.ss.usermodel.DateUtil.isCellDateFormatted(xssfCell)) {
				Date date = org.apache.poi.ss.usermodel.DateUtil.getJavaDate(xssfCell.getNumericCellValue());
				cellValue = sdf.format(date);
			} else {
				DecimalFormat df = new DecimalFormat("#.##");
				cellValue = df.format(xssfCell.getNumericCellValue());
				String strArr = cellValue.substring(cellValue.lastIndexOf(POINT) + 1, cellValue.length());
				if ("00".equals(strArr)) {
					cellValue = cellValue.substring(0, cellValue.lastIndexOf(POINT));
				}
			}
			return cellValue;
		} else {
			return String.valueOf(xssfCell.getStringCellValue());
		}
	}

	public static <T> List<T> readXls(File file, Class<T> clazz) throws InstantiationException, IllegalAccessException,
			IOException, NoSuchMethodException, SecurityException, IllegalArgumentException, InvocationTargetException {
		HSSFWorkbook hssfWorkbook = new HSSFWorkbook(new FileInputStream(file));
		List<T> listT = new ArrayList<T>();
		// 循环工作表Sheet
		for (int numSheet = 0; numSheet < hssfWorkbook.getNumberOfSheets(); numSheet++) {
			HSSFSheet hssfSheet = hssfWorkbook.getSheetAt(numSheet);
			if (hssfSheet == null) {
				continue;
			}
			initSheetMerged(hssfSheet);
			// 循环行Row
			for (int rowNum = 0; rowNum <= hssfSheet.getLastRowNum(); rowNum++) {
				HSSFRow hssfRow = hssfSheet.getRow(rowNum);
				if (hssfRow != null) {
					T t = clazz.newInstance();
					Field[] fields = clazz.getDeclaredFields();
					for (int i = 0; i < fields.length; i++) {
						HSSFCell hssfCell = hssfRow.getCell(i);
						String methodName = "set" + fields[i].getName().substring(0, 1).toUpperCase()
								+ fields[i].getName().substring(1, fields[i].getName().length());
						Method method = clazz.getDeclaredMethod(methodName, fields[i].getType());
						// method.invoke(t, getValue(hssfCell));
						Class<?> fieldType = fields[i].getType();
						String fieldValue = getValue(hssfCell);
						if (fieldType != null) {
							try {
								if (String.class.equals(fieldType)) {
									method.invoke(t, fieldValue);
								} else if (byte.class.equals(fieldType)) {
									method.invoke(t, Byte.parseByte(fieldValue));

								} else if (Byte.class.equals(fieldType)) {
									method.invoke(t, Byte.valueOf(fieldValue));

								} else if (boolean.class.equals(fieldType)) {
									method.invoke(t, Boolean.parseBoolean(fieldValue));

								} else if (Boolean.class.equals(fieldType)) {
									method.invoke(t, Boolean.valueOf(fieldValue));

								} else if (short.class.equals(fieldType)) {
									method.invoke(t, Short.parseShort(fieldValue));

								} else if (Short.class.equals(fieldType)) {
									method.invoke(t, Short.valueOf(fieldValue));

								} else if (int.class.equals(fieldType)) {
									method.invoke(t, Integer.parseInt(fieldValue));

								} else if (Integer.class.equals(fieldType)) {
									method.invoke(t, Integer.valueOf(fieldValue));

								} else if (long.class.equals(fieldType)) {
									method.invoke(t, Long.parseLong(fieldValue));

								} else if (Long.class.equals(fieldType)) {
									method.invoke(t, Long.valueOf(fieldValue));

								} else if (float.class.equals(fieldType)) {
									method.invoke(t, Float.parseFloat(fieldValue));

								} else if (Float.class.equals(fieldType)) {
									method.invoke(t, Float.valueOf(fieldValue));

								} else if (double.class.equals(fieldType)) {
									method.invoke(t, Double.parseDouble(fieldValue));

								} else if (Double.class.equals(fieldType)) {
									method.invoke(t, Double.valueOf(fieldValue));

								} else if (Date.class.equals(fieldType)) {
									SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss.SSS");
									method.invoke(t, sdf.parse(fieldValue));
								}
							} catch (IllegalArgumentException e) {
								e.printStackTrace();
							} catch (ParseException e) {
								e.printStackTrace();
							}

						}

					}
					listT.add(t);
				}
			}
		}
		return listT;
	}

	public static <T> List<T> readExcel(File file, Class<T> clazz, String fileContentType) throws Exception {
		if ("xls".equals(fileContentType)) {
			return readXls(file, clazz);
		} else if ("xlsx".equals(fileContentType)) {
			return readXlsx(file, clazz);
		}
		return null;

	}

	public static List<List<String>> readExcel(File file, String fileContentType, int startRow)
			throws FileNotFoundException, IOException {
		if ("xls".equals(fileContentType)) {
			return readXls(file, startRow);
		} else if ("xlsx".equals(fileContentType)) {
			return readXlsx(file, startRow);
		}
		return null;
	}

	public static List<List<String>> readExcelBySheetName(File file, String fileContentType, int startRow,
			String sheetName) throws FileNotFoundException, IOException {
		if ("xls".equals(fileContentType)) {
			return readXlsBySheetName(file, startRow, sheetName);
		} else if ("xlsx".equals(fileContentType)) {
			return readXlsxBySheetName(file, startRow, sheetName);
		}
		return null;
	}

	public static List<List<String>> readExcel(File file, String fileContentType)
			throws FileNotFoundException, IOException {
		return readExcel(file, fileContentType, 0);
	}

	public static List<List<String>> readExcel(File file, String fileContentType, String sheetName)
			throws FileNotFoundException, IOException {
		return readExcelBySheetName(file, fileContentType, 0, sheetName);
	}

	/**
	 * <p>
	 * Title: readXlsx
	 * </p>
	 * <p>
	 * Descriptions:
	 * </p>
	 * <p>
	 * Company:星海
	 * </p>
	 * 
	 * @author 陈刚
	 * @date 2017-4-5 上午11:18:26
	 * @param file
	 * @return
	 * @throws IOException
	 * @throws FileNotFoundException
	 */

	private static List<List<String>> readXlsx(File file, int startRow) throws FileNotFoundException, IOException {

		System.out.println("readXlsx");
		XSSFWorkbook xssfWorkbook = null;
		xssfWorkbook = new XSSFWorkbook(new FileInputStream(file));
		if (xssfWorkbook == null) {
            return null;
        }
		List<List<String>> listT = new ArrayList<List<String>>();
		// 循环工作表Sheet
		for (int numSheet = 0; numSheet < xssfWorkbook.getNumberOfSheets(); numSheet++) {
			XSSFSheet xssfSheet = xssfWorkbook.getSheetAt(numSheet);
			if (xssfSheet == null) {
				continue;
			}
			initSheetMerged(xssfSheet);
			// 循环行Row
			for (int rowNum = startRow; rowNum <= xssfSheet.getLastRowNum(); rowNum++) {
				XSSFRow xssfRow = xssfSheet.getRow(rowNum);
				if (xssfRow != null) {
					List<String> row = new ArrayList<String>();
					for (int i = 0; i < xssfRow.getLastCellNum(); i++) {
						XSSFCell xssfCell = xssfRow.getCell(i);
						if(null==xssfCell) {
							row.add("");
						}else {
						xssfCell.setCellType(XSSFCell.CELL_TYPE_STRING);
						String fieldValue = getValue(xssfCell);
						row.add(fieldValue);
						}
					}
					listT.add(row);
				}
			}
		}
		return listT;
	}

	private static List<List<String>> readXlsxBySheetName(File file, int startRow, String sheetName)
			throws FileNotFoundException, IOException {

		System.out.println("readXlsx");
		XSSFWorkbook xssfWorkbook = null;
		xssfWorkbook = new XSSFWorkbook(new FileInputStream(file));
		if (xssfWorkbook == null) {
            return null;
        }
		List<List<String>> listT = new ArrayList<List<String>>();
		// 循环工作表Sheet
		for (int numSheet = 0; numSheet < xssfWorkbook.getNumberOfSheets(); numSheet++) {
			XSSFSheet xssfSheet = xssfWorkbook.getSheetAt(numSheet);
			if (xssfSheet == null) {
				continue;
			}
			if (!sheetName.equals(xssfSheet.getSheetName())) {
				continue;
			}
			initSheetMerged(xssfSheet);
			// 循环行Row
			for (int rowNum = startRow; rowNum <= xssfSheet.getLastRowNum(); rowNum++) {
				XSSFRow xssfRow = xssfSheet.getRow(rowNum);
				if (xssfRow != null) {
					List<String> row = new ArrayList<String>();

					for (int i = 0; i < xssfRow.getLastCellNum(); i++) {
						XSSFCell xssfCell = xssfRow.getCell(i);
						String fieldValue = getValue(xssfCell);
						row.add(fieldValue);
					}
					listT.add(row);
				}
			}
		}
		return listT;
	}

	public static HSSFWorkbook readHSSFWorkbook(String filePath) {
		HSSFWorkbook hssfWorkbook = null;
		try {
			hssfWorkbook = new HSSFWorkbook(new FileInputStream(new File(filePath)));
		} catch (FileNotFoundException e) {
			e.printStackTrace();
		} catch (IOException e) {
			e.printStackTrace();
		}
		return hssfWorkbook;
	}

	/**
	 * <p>
	 * Title: readXls
	 * </p>
	 * <p>
	 * Descriptions:
	 * </p>
	 * <p>
	 * Company:星海
	 * </p>
	 * 
	 * @author 陈刚
	 * @date 2017-4-5 上午11:08:48
	 * @return
	 * @throws IOException
	 * @throws FileNotFoundException
	 */

	@SuppressWarnings("resource")
	private static List<List<String>> readXls(File file, int startRow) throws FileNotFoundException, IOException {
		HSSFWorkbook hssfWorkbook = null;
		hssfWorkbook = new HSSFWorkbook(new FileInputStream(file));
		List<List<String>> listT = new ArrayList<List<String>>();
		// 循环工作表Sheet
		for (int numSheet = 0; numSheet < hssfWorkbook.getNumberOfSheets(); numSheet++) {
			HSSFSheet hssfSheet = hssfWorkbook.getSheetAt(numSheet);
			if (hssfSheet == null) {
				continue;
			}
			initSheetMerged(hssfSheet);
			// 循环行Row
			for (int rowNum = startRow; rowNum <= hssfSheet.getLastRowNum(); rowNum++) {
				HSSFRow hssfRow = hssfSheet.getRow(rowNum);
				if (hssfRow != null) {
					List<String> row = new ArrayList<String>();
					for (int i = 0; i < hssfRow.getLastCellNum(); i++) {
						HSSFCell hssfCell = hssfRow.getCell(i);
						if(null==hssfCell) {
							row.add("");
						}else {
							hssfCell.setCellType(HSSFCell.CELL_TYPE_STRING);
							String fieldValue = getValue(hssfCell);
							row.add(fieldValue);
						}
					}
					listT.add(row);
				}
			}
		}
		return listT;
	}

	@SuppressWarnings({ "resource", "unused" })
	private static List<List<String>> readXlsBySheetName(File file, int startRow, String sheetName)
			throws FileNotFoundException, IOException {
		HSSFWorkbook hssfWorkbook = null;
		hssfWorkbook = new HSSFWorkbook(new FileInputStream(file));
		if (hssfWorkbook == null) {
            return null;
        }
		List<List<String>> listT = new ArrayList<List<String>>();
		// 循环工作表Sheet
		for (int numSheet = 0; numSheet < hssfWorkbook.getNumberOfSheets(); numSheet++) {
			HSSFSheet hssfSheet = hssfWorkbook.getSheetAt(numSheet);
			if (hssfSheet == null) {
				continue;
			}
			if (!sheetName.equals(hssfSheet.getSheetName())) {
				continue;
			}
			initSheetMerged(hssfSheet);
			// 循环行Row
			for (int rowNum = startRow; rowNum <= hssfSheet.getLastRowNum(); rowNum++) {
				HSSFRow hssfRow = hssfSheet.getRow(rowNum);
				if (hssfRow != null) {
					List<String> row = new ArrayList<String>();
					for (int i = 0; i < hssfRow.getLastCellNum(); i++) {
						HSSFCell hssfCell = hssfRow.getCell(i);
						String fieldValue = getValue(hssfCell);
						row.add(fieldValue);
					}
					listT.add(row);
				}
			}
		}
		return listT;
	}
	@SuppressWarnings({ "resource", "unused" })
	public static List<List<String>> readXlsBySheetName(HSSFWorkbook hssfWorkbook, int startRow, int sheetIndex) {
		List<List<String>> listT = new ArrayList<List<String>>();
		// 循环工作表Sheet
		HSSFSheet hssfSheet = hssfWorkbook.getSheetAt(sheetIndex);
		initSheetMerged(hssfSheet);
		// 循环行Row
		for (int rowNum = startRow; rowNum <= hssfSheet.getLastRowNum(); rowNum++) {
			HSSFRow hssfRow = hssfSheet.getRow(rowNum);
			if (hssfRow != null) {
				List<String> row = new ArrayList<String>();
				for (int i = 0; i < hssfRow.getLastCellNum(); i++) {
					HSSFCell hssfCell = hssfRow.getCell(i);
					String fieldValue = getValue(hssfCell);
					row.add(fieldValue);
				}
				listT.add(row);
			}
		}
		return listT;
	}

	public static <T> HSSFWorkbook writeXls(List<T> listT) throws IOException, NoSuchMethodException, SecurityException,
			IllegalAccessException, IllegalArgumentException, InvocationTargetException, ParseException {

		// 创建HSSFWorkbook对象
		HSSFWorkbook wb = new HSSFWorkbook();
		// 创建HSSFSheet对象
		HSSFSheet sheet = wb.createSheet("sheet0");
		for (int i = 0; i < listT.size(); i++) {
			// 创建HSSFRow对象
			HSSFRow row = sheet.createRow(i);
			T t = listT.get(i);
			Field[] fields = t.getClass().getDeclaredFields();
			for (int j = 0; j < fields.length; j++) {

				// 创建HSSFCell对象
				HSSFCell cell = row.createCell(j);
				String methodName = "get" + fields[j].getName().substring(0, 1).toUpperCase()
						+ fields[j].getName().substring(1, fields[j].getName().length());
				Method method = t.getClass().getDeclaredMethod(methodName);
				Class<?> fieldType = fields[j].getType();
				Object cellValue = method.invoke(t);

				/* cell.setCellValue(cellValue); */

				setCellValueByFieldType(cell, fieldType, cellValue);

			}
		}
		// 输出Excel文件
		/*
		 * FileOutputStream output=new FileOutputStream(path); wb.write(output);
		 * output.flush(); return "success";
		 */
		return wb;
	}

	public static <T> HSSFWorkbook writeXls(List<T> listT, List<String> fieldList, List<String> chineseFieldList,
			Object... string) throws Exception {
		String sheetName = "sheet0";
		if (string.length > 0) {
			sheetName = StringUtils.isEmpty((String) string[0]) ? "sheet0" : (String) string[0];
		}
		// 创建HSSFWorkbook对象
		HSSFWorkbook wb = new HSSFWorkbook();
		// 创建HSSFSheet对象
		List<List<String>> chineseFieldListList = new ArrayList<List<String>>();
		chineseFieldListList.add(chineseFieldList);
		wb = ExcelUtil.writeXlsByValueList(chineseFieldListList, sheetName);
		HSSFSheet sheet = wb.getSheet(sheetName);
		for (int i = 0; i < listT.size(); i++) {
			// 创建HSSFRow对象
			HSSFRow row = sheet.createRow(i + 1);
			T t = listT.get(i);
			for (int j = 0; j < fieldList.size(); j++) {
				// 创建HSSFCell对象
				HSSFCell cell = row.createCell(j);
				String methodName = "get" + fieldList.get(j).substring(0, 1).toUpperCase()
						+ fieldList.get(j).substring(1, fieldList.get(j).length());
				Method method = ReflectUtil.getDeclaredMethod(t, methodName);
				System.out.println("导出字段【" + chineseFieldList.get(j) + "/" + fieldList.get(j) + "】");
				Class<?> fieldType = ReflectUtil.getDeclaredField(t, fieldList.get(j)).getType();
				Object cellValue = method.invoke(t);
				setCellValueByFieldType(cell, fieldType, cellValue);
			}
		}
		setAutoWeight(sheet, sheet.getRow(0).getPhysicalNumberOfCells());
		setSizeColumn(sheet, listT.size());
		return wb;
	}

	public static <T> HSSFWorkbook writeXls(HSSFWorkbook wb, List<T> listT, List<String> fieldList,
			List<String> chineseFieldList, Object... string) throws Exception {
		String sheetName = "sheet0";
		if (string.length > 0) {
			sheetName = StringUtils.isEmpty((String) string[0]) ? "sheet0" : (String) string[0];
		}
		// 创建HSSFSheet对象
		List<List<String>> chineseFieldListList = new ArrayList<List<String>>();
		chineseFieldListList.add(chineseFieldList);
		wb = ExcelUtil.writeXlsByValueList(wb, chineseFieldListList, sheetName);
		HSSFSheet sheet = wb.getSheet(sheetName);
		HSSFCellStyle cellStyle = wb.createCellStyle();
		cellStyle.setBorderLeft(BorderStyle.THIN);
		cellStyle.setBorderRight(BorderStyle.THIN);
		cellStyle.setBorderTop(BorderStyle.THIN);
		cellStyle.setBorderBottom(BorderStyle.THIN);
		//陈刚，暂时注释掉
//		cellStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);
//		cellStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
		cellStyle.setWrapText(true);
		sheet.autoSizeColumn(1);
		for (int i = 0; i < listT.size(); i++) {
			// 创建HSSFRow对象
			HSSFRow row = sheet.createRow(i + 1);
			T t = listT.get(i);
			for (int j = 0; j < fieldList.size(); j++) {
				// 创建HSSFCell对象
				HSSFCell cell = row.createCell(j);
				cell.setCellStyle(cellStyle);
				String methodName = "get" + fieldList.get(j).substring(0, 1).toUpperCase()
						+ fieldList.get(j).substring(1, fieldList.get(j).length());
				Method method = ReflectUtil.getDeclaredMethod(t, methodName);
				System.out.println("写入字段【" + chineseFieldList.get(j) + "/" + fieldList.get(j) + "】");
				Class<?> fieldType = ReflectUtil.getDeclaredField(t, fieldList.get(j)).getType();
				Object cellValue = method.invoke(t);
				setCellValueByFieldType(cell, fieldType, cellValue);
			}
		}
		setSizeColumn(sheet, fieldList.size());
		return wb;
	}

	/**
	 * 
	 * <p>
	 * Title: writeXlsByValueList
	 * </p>
	 * <p>
	 * Descriptions: 将List<List<String>>写入HSSFWorkbook，sheet="sheet0",从第0行，第0列开始
	 * </p>
	 * <p>
	 * Company:星海
	 * </p>
	 * 
	 * @author 陈刚
	 * @date 2017-5-5 上午10:04:54
	 * @param list
	 * @return
	 */
	public static HSSFWorkbook writeXlsByValueList(List<List<String>> list, Object... string) {
		String sheetName = "sheet0";
		if (string.length > 0) {
			sheetName = StringUtils.isEmpty((String) string[0]) ? "sheet0" : (String) string[0];
		}
		// 创建HSSFWorkbook对象
		HSSFWorkbook wb = new HSSFWorkbook();
		// 创建HSSFSheet对象
		HSSFSheet sheet = wb.createSheet(sheetName);
		
		/////////
		HSSFPatriarch patriarch = sheet.createDrawingPatriarch();
		/////////
		
		HSSFCellStyle hssfCellStyle = wb.createCellStyle();

		hssfCellStyle.setBorderLeft(BorderStyle.THIN);
		hssfCellStyle.setBorderRight(BorderStyle.THIN);
		hssfCellStyle.setBorderTop(BorderStyle.THIN);
		hssfCellStyle.setBorderBottom(BorderStyle.THIN);
		for (int i = 0; i < list.size(); i++) {
			// 创建HSSFRow对象
			HSSFRow row = sheet.createRow(i);
			if(i > 0) {
                row.setHeightInPoints(40);
            }
			List<String> list2 = list.get(i);
			for (int j = 0; j < list2.size(); j++) {
				// 创建HSSFCell对象
				HSSFCell cell = row.createCell(j);
				cell.setCellValue(String.valueOf(list2.get(j)));
				cell.setCellStyle(hssfCellStyle);
				
				
				
				if(list2.get(j) != null){
					if(list2.get(j).toString().length() > 36){
						try {
							cell.setCellValue("");
							String[] vals = list2.get(j).split(",");
							if(vals.length > 1){
								for (int h=0;h<vals.length;h++) {
									if(h>0){
										HSSFCell celll = row.createCell(j+h);
										celll.setCellValue("");
										celll.setCellStyle(hssfCellStyle);
									}
									InputStream is = new FileInputStream(new File(vals[h].toString()));
									ByteArrayOutputStream os = new ByteArrayOutputStream();
									byte[] bytes = new byte[1024];
									int len = 0;
									while((len=is.read(bytes)) != -1){
										os.write(bytes, 0, len);
									}
									is.close();
									byte[] picData = os.toByteArray();
									HSSFClientAnchor anchor = new HSSFClientAnchor(0, 0, 1023, 255, (short)(j+h), i, (short)(j+h), i);
									anchor.setAnchorType(0);
									patriarch.createPicture(anchor, wb.addPicture(picData, HSSFWorkbook.PICTURE_TYPE_JPEG));
								}
							}else{
								
								InputStream is = new FileInputStream(new File(vals[0].toString()));
								ByteArrayOutputStream os = new ByteArrayOutputStream();
								byte[] bytes = new byte[1024];
								int len = 0;
								while((len=is.read(bytes)) != -1){
									os.write(bytes, 0, len);
								}
								is.close();
								byte[] picData = os.toByteArray();
								HSSFClientAnchor anchor = new HSSFClientAnchor(0, 0, 1023, 255, (short)j, i, (short)j, i);
								anchor.setAnchorType(0);
								patriarch.createPicture(anchor, wb.addPicture(picData, HSSFWorkbook.PICTURE_TYPE_JPEG));
							
							}
						} catch (Exception e) {
							e.printStackTrace();
						}
						
					}
				}

				
			}
			sheet.autoSizeColumn(i);
			
			sheet.setColumnWidth(7, 20*256);
		}
		// 输出Excel文件
		/*
		 * FileOutputStream output=new FileOutputStream(path); wb.write(output);
		 * output.flush(); return "success";
		 */
		return wb;
	}

	public static HSSFWorkbook writeXlsByValueList(HSSFWorkbook wb, List<List<String>> list, Object... string) {
		String sheetName = "sheet0";
		if (string.length > 0) {
			sheetName = StringUtils.isEmpty((String) string[0]) ? "sheet0" : (String) string[0];
		}
		// 创建HSSFSheet对象
		HSSFSheet sheet = wb.createSheet(sheetName);
		HSSFCellStyle hssfCellStyle = wb.createCellStyle();
		hssfCellStyle.setBorderLeft(BorderStyle.THIN);
		hssfCellStyle.setBorderRight(BorderStyle.THIN);
		hssfCellStyle.setBorderTop(BorderStyle.THIN);
		hssfCellStyle.setBorderBottom(BorderStyle.THIN);
		//陈刚，暂时注释掉
//		hssfCellStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);
//		hssfCellStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
		hssfCellStyle.setWrapText(true);
		HSSFFont font = wb.createFont();
		font.setBold(true);
		font.setFontHeightInPoints((short) 11);
		hssfCellStyle.setFont(font);
		for (int i = 0; i < list.size(); i++) {
			// 创建HSSFRow对象
			HSSFRow row = sheet.createRow(i);
			List<String> list2 = list.get(i);
			for (int j = 0; j < list2.size(); j++) {
				// 创建HSSFCell对象
				HSSFCell cell = row.createCell(j);
				cell.setCellValue(String.valueOf(list2.get(j)));
				cell.setCellStyle(hssfCellStyle);
			}
		}
		return wb;
	}

	public static HSSFWorkbook writeXlsByValueListAndHasCellRangeAddress(List<List<String>> list,
			List<CellRangeAddress> cellRangeAddressesList) {

		// 创建HSSFWorkbook对象
		HSSFWorkbook wb = new HSSFWorkbook();
		// 创建HSSFSheet对象
		HSSFSheet sheet = wb.createSheet("sheet0");
		// 加载合并单元格对象
		if (cellRangeAddressesList != null && cellRangeAddressesList.size() > 0) {
			for (CellRangeAddress cellRangeAddress : cellRangeAddressesList) {
				sheet.addMergedRegion(cellRangeAddress);
			}
		}

		HSSFCellStyle hssfCellStyle = wb.createCellStyle();

		hssfCellStyle.setBorderLeft(BorderStyle.THIN);
		hssfCellStyle.setBorderRight(BorderStyle.THIN);
		hssfCellStyle.setBorderTop(BorderStyle.THIN);
		hssfCellStyle.setBorderBottom(BorderStyle.THIN);
		for (int i = 0; i < list.size(); i++) {
			// 创建HSSFRow对象
			HSSFRow row = sheet.createRow(i);
			List<String> list2 = list.get(i);
			for (int j = 0; j < list2.size(); j++) {

				// 创建HSSFCell对象
				HSSFCell cell = row.createCell(j);
				cell.setCellValue(String.valueOf(list2.get(j)));
				cell.setCellStyle(hssfCellStyle);
			}
		}
		// 输出Excel文件
		/*
		 * FileOutputStream output=new FileOutputStream(path); wb.write(output);
		 * output.flush(); return "success";
		 */
		return wb;
	}

	/**
	 * 
	 * <p>
	 * Title: writeXlsByValueList
	 * </p>
	 * <p>
	 * Descriptions: 将List<List<String>>写入HSSFWorkbook，sheet="sheet0",从第0行，第0列开始
	 * </p>
	 * <p>
	 * Company:星海
	 * </p>
	 * 
	 * @author 陈刚
	 * @date 2017-5-5 上午10:04:54
	 * @param list
	 * @return
	 */
	public static HSSFWorkbook writeXlsByValueListHasBorder(List<List<String>> list) {

		// 创建HSSFWorkbook对象
		HSSFWorkbook wb = new HSSFWorkbook();
		// 创建HSSFSheet对象
		HSSFSheet sheet = wb.createSheet("sheet0");
		HSSFCellStyle hssfCellStyle = wb.createCellStyle();
		hssfCellStyle.setBorderLeft(BorderStyle.THIN);
		hssfCellStyle.setBorderRight(BorderStyle.THIN);
		hssfCellStyle.setBorderTop(BorderStyle.THIN);
		hssfCellStyle.setBorderBottom(BorderStyle.THIN);

		for (int i = 0; i < list.size(); i++) {
			// 创建HSSFRow对象
			HSSFRow row = sheet.createRow(i);
			List<String> list2 = list.get(i);
			for (int j = 0; j < list2.size(); j++) {

				// 创建HSSFCell对象
				HSSFCell cell = row.createCell(j);
				cell.setCellValue(String.valueOf(list2.get(j)));
				cell.setCellStyle(hssfCellStyle);
			}
		}
		// 输出Excel文件
		/*
		 * FileOutputStream output=new FileOutputStream(path); wb.write(output);
		 * output.flush(); return "success";
		 */
		return wb;
	}

	public static HSSFWorkbook getWorkbookHasBorderStyle() {
		HSSFWorkbook wb = new HSSFWorkbook();
		HSSFCellStyle hssfCellStyle = wb.createCellStyle();
		hssfCellStyle.setBorderLeft(BorderStyle.THIN);
		hssfCellStyle.setBorderRight(BorderStyle.THIN);
		hssfCellStyle.setBorderTop(BorderStyle.THIN);
		hssfCellStyle.setBorderBottom(BorderStyle.THIN);
		return wb;
	}

	/**
	 * 
	 * <p>
	 * Title: writeXlsByValueList
	 * </p>
	 * <p>
	 * Descriptions:
	 * 将List<List<String>>写入HSSFWorkbook，sheet=sheetName,从第rowStart行，第columnStart列开始
	 * </p>
	 * <p>
	 * Company:星海
	 * </p>
	 * 
	 * @author 陈刚
	 * @date 2017-5-5 上午10:11:04
	 * @param hssfWorkbook
	 * @param list
	 * @param sheetName
	 * @param rowStart
	 * @param columnStart
	 * @return
	 */
	public static HSSFWorkbook writeXlsByValueList(HSSFWorkbook hssfWorkbook, List<List<String>> list, String sheetName,
			int rowStart, int columnStart) {
		if (hssfWorkbook == null) {
			hssfWorkbook = new HSSFWorkbook();
		}
		// 创建HSSFSheet对象
		HSSFSheet sheet = hssfWorkbook.createSheet(sheetName);
		for (int i = 0; i < list.size(); i++) {
			// 创建HSSFRow对象
			HSSFRow row = sheet.createRow(i + rowStart);
			List<String> list2 = list.get(i);
			for (int j = 0; j < list2.size(); j++) {

				// 创建HSSFCell对象
				HSSFCell cell = row.createCell(j + columnStart);
				cell.setCellValue(String.valueOf(list2.get(j)));
			}
		}
		return hssfWorkbook;
	}

	/**
	 * 
	 * <p>
	 * Title: writeXlsByValueList
	 * </p>
	 * <p>
	 * Descriptions: 将mapList转为listList，然后调用public HSSFWorkbook
	 * writeXlsByValueList(HSSFWorkbook hssfWorkbook,List<List<String>> list,String
	 * sheetName,int rowStart ,int columnStart)方法
	 * </p>
	 * <p>
	 * Company:星海
	 * </p>
	 * 
	 * @author 陈刚
	 * @date 2017-5-22 下午03:12:07
	 * @param hssfWorkbook
	 * @param mapList
	 * @param sortList
	 * @param sheetName
	 * @param rowStart
	 * @param columnStart
	 * @return
	 */
	public static HSSFWorkbook writeXlsByValueList(HSSFWorkbook hssfWorkbook, List<Map<String, Object>> mapList,
			List<String> sortList, String sheetName, int rowStart, int columnStart) {
		List<List<String>> list = ExcelUtil.mapListToListList(mapList, sortList);
		return writeXlsByValueList(hssfWorkbook, list, sheetName, rowStart, columnStart);
	}

	/**
	 * 
	 * <p>
	 * Title: writeXlsByValueList
	 * </p>
	 * <p>
	 * Descriptions: 将mapList转为listList，然后调用public HSSFWorkbook
	 * writeXlsByValueList(HSSFWorkbook hssfWorkbook,List<List<String>> list,String
	 * sheetName,int rowStart ,int columnStart)方法
	 * </p>
	 * <p>
	 * Company:星海
	 * </p>
	 * 
	 * @author 陈刚
	 * @date 2017-5-22 下午03:17:16
	 * @param mapList
	 * @param sortList
	 * @param sheetName
	 * @param rowStart
	 * @param columnStart
	 * @return
	 */
	public static HSSFWorkbook writeXlsByValueList(List<Map<String, Object>> mapList, List<String> sortList,
			String sheetName, int rowStart, int columnStart) {
		List<List<String>> list = ExcelUtil.mapListToListList(mapList, sortList);
		return writeXlsByValueList(null, list, sheetName, rowStart, columnStart);
	}

	/**
	 * 
	 * <p>
	 * Title: writeXlsByValueList
	 * </p>
	 * <p>
	 * Descriptions:
	 * 将List<List<String>>写入HSSFWorkbook，sheet=HSSFWorkbook中的第sheetNumber个sheet,从第rowStart行，第columnStart列开始
	 * </p>
	 * <p>
	 * Company:星海
	 * </p>
	 * 
	 * @author 陈刚
	 * @date 2017-5-5 上午10:52:31
	 * @param hssfWorkbook
	 * @param list
	 * @param sheetNumber
	 * @param rowStart
	 * @param columnStart
	 * @return
	 */
	public static HSSFWorkbook writeXlsByValueList(HSSFWorkbook hssfWorkbook, List<List<String>> list, int sheetNumber,
			int rowStart, int columnStart) {
		if (hssfWorkbook == null) {
			hssfWorkbook = new HSSFWorkbook();
			hssfWorkbook.createSheet();
		}
		// 创建HSSFSheet对象

		HSSFSheet sheet = hssfWorkbook.getSheetAt(sheetNumber);
		for (int i = 0; i < list.size(); i++) {
			// 创建HSSFRow对象
			HSSFRow row = sheet.getRow(i + rowStart);
			if (row == null) {
				row = sheet.createRow(i + rowStart);
			}
			List<String> list2 = list.get(i);
			for (int j = 0; j < list2.size(); j++) {

				// 创建HSSFCell对象
				HSSFCell cell = row.getCell(j + columnStart);
				if (cell == null) {
					cell = row.createCell(j + columnStart);
				}
				/*
				 * 设置样式 HSSFFont font = hssfWorkbook.createFont();
				 * font.setFontHeightInPoints((short) 8); CellStyle cellStyle =
				 * cell.getCellStyle(); cellStyle.setFont(font); cell.setCellStyle(cellStyle);
				 */
				cell.setCellValue(String.valueOf(list2.get(j)));
			}
		}
		return hssfWorkbook;
	}

	public static <T> HSSFWorkbook writeXls(HSSFWorkbook hssfWorkbook, List<T> listT, List<String> fieldList,
			int rowStart, int colStart)
			throws IOException, NoSuchMethodException, SecurityException, IllegalAccessException,
			IllegalArgumentException, InvocationTargetException, ParseException, NoSuchFieldException {

		// 创建HSSFWorkbook对象
		HSSFWorkbook wb = hssfWorkbook;
		// 创建HSSFSheet对象
		HSSFSheet sheet = wb.getSheetAt(0);
		for (int i = 0; i < listT.size(); i++) {
			// 创建HSSFRow对象
			HSSFRow row = sheet.createRow(i + rowStart);
			T t = listT.get(i);
			for (int j = 0; j < fieldList.size(); j++) {

				// 创建HSSFCell对象
				HSSFCell cell = row.createCell(j + colStart);
				String methodName = "get" + fieldList.get(j).substring(0, 1).toUpperCase()
						+ fieldList.get(j).substring(1, fieldList.get(j).length());
				Method method = t.getClass().getDeclaredMethod(methodName);
				Class<?> fieldType = t.getClass().getDeclaredField(fieldList.get(j)).getType();
				Object cellValue = method.invoke(t);
				/* cell.setCellValue(cellValue); */

				setCellValueByFieldType(cell, fieldType, cellValue);

			}
		}
		// 输出Excel文件
		/*
		 * FileOutputStream output=new FileOutputStream(path); wb.write(output);
		 * output.flush(); return "success";
		 */
		return wb;
	}

	/**
	 * <p>
	 * setCellValueByFieldType:()
	 * </p>
	 * 
	 * @description
	 * @author 陈刚
	 * @date 2018年8月28日 下午3:06:44
	 * @param cell
	 * @param fieldType
	 * @param cellValue
	 * @throws ParseException
	 */
	private static void setCellValueByFieldType(HSSFCell cell, Class<?> fieldType, Object cellValue)
			throws ParseException {
		if (cellValue == null) {
			cell.setCellValue("");
			return;
		}
		if (String.class.equals(fieldType)) {
			cell.setCellValue(String.valueOf(cellValue));
		} else if (byte.class.equals(fieldType)) {
			cell.setCellValue((Byte) (cellValue));

		} else if (Byte.class.equals(fieldType)) {
			cell.setCellValue((Byte) (cellValue));

		} else if (boolean.class.equals(fieldType)) {
			cell.setCellValue((Boolean) (cellValue));

		} else if (Boolean.class.equals(fieldType)) {
			cell.setCellValue((Boolean) (cellValue));

		} else if (short.class.equals(fieldType)) {
			cell.setCellValue((Short) (cellValue));

		} else if (Short.class.equals(fieldType)) {
			cell.setCellValue((Short) (cellValue));

		} else if (int.class.equals(fieldType)) {
			cell.setCellValue((Integer) (cellValue));

		} else if (Integer.class.equals(fieldType)) {
			cell.setCellValue((Integer) (cellValue));

		} else if (long.class.equals(fieldType)) {
			cell.setCellValue((Long) (cellValue));

		} else if (Long.class.equals(fieldType)) {
			cell.setCellValue((Long) (cellValue));

		} else if (float.class.equals(fieldType)) {
			cell.setCellValue((Float) (cellValue));

		} else if (Float.class.equals(fieldType)) {
			cell.setCellValue((Float) (cellValue));

		} else if (double.class.equals(fieldType)) {
			cell.setCellValue((Double) (cellValue));

		} else if (Double.class.equals(fieldType)) {
			cell.setCellValue((Double) (cellValue));

		} else if (Date.class.equals(fieldType)) {
			SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss.SSS");
			cell.setCellValue(sdf.parse((String) cellValue));
		}
	}

	/**
	 * 
	 * <p>
	 * Title: getByteArrayInputStream
	 * </p>
	 * <p>
	 * Descriptions:
	 * </p>
	 * <p>
	 * Company:星海
	 * </p>
	 * 
	 * @author 陈刚
	 * @date 2017-4-11 上午09:45:55
	 * @param workbook
	 * @return
	 * @throws IOException
	 */

	public static ByteArrayInputStream getByteArrayInputStream(Workbook workbook) throws IOException {
		ByteArrayOutputStream byteArrayOutputStream = new ByteArrayOutputStream();
		workbook.write(byteArrayOutputStream);
		byteArrayOutputStream.flush();
		ByteArrayInputStream byteArrayInputStream = new ByteArrayInputStream(byteArrayOutputStream.toByteArray());
		return byteArrayInputStream;
	}

	/**
	 * 
	 * <p>
	 * Title: getByteArrayInputStream
	 * </p>
	 * <p>
	 * Descriptions:
	 * </p>
	 * <p>
	 * Company:星海
	 * </p>
	 * 
	 * @author 陈刚
	 * @date 2017-4-11 上午09:45:55
	 * @param workbook
	 * @return
	 * @throws IOException
	 */

	public static ByteArrayInputStream getByteArrayInputStreamStatic(Workbook workbook) throws IOException {
		ByteArrayOutputStream byteArrayOutputStream = new ByteArrayOutputStream();
		workbook.write(byteArrayOutputStream);
		byteArrayOutputStream.flush();
		ByteArrayInputStream byteArrayInputStream = new ByteArrayInputStream(byteArrayOutputStream.toByteArray());
		return byteArrayInputStream;
	}

	/**
	 * <p>
	 * Title: mapListToListList
	 * </p>
	 * <p>
	 * Descriptions: 将mapList转listList,无排序
	 * </p>
	 * <p>
	 * Company:星海
	 * </p>
	 * 
	 * @author 陈刚
	 * @date 2017-4-20 上午10:49:47
	 * @param mapList
	 * @return
	 */

	public static List<List<String>> mapListToListList(List<Map<String, Object>> mapList) {
		List<List<String>> result = new ArrayList<List<String>>();
		for (int i = 0; i < mapList.size(); i++) {
			Map<String, Object> map = mapList.get(i);
			List<String> list = new ArrayList<String>();
			for (Entry<String, Object> entry : map.entrySet()) {
				list.add(entry.getValue() == null ? "" : entry.getValue().toString());
			}
			result.add(list);
		}
		return result;
	}

	public static List<List<String>> mapListStringToListList(List<Map<String, String>> mapList) {
		List<List<String>> result = new ArrayList<List<String>>();
		for (int i = 0; i < mapList.size(); i++) {
			Map<String, String> map = mapList.get(i);
			List<String> list = new ArrayList<String>();
			for (Entry<String, String> entry : map.entrySet()) {
				list.add(entry.getValue() == null ? "" : entry.getValue().toString());
			}
			result.add(list);
		}
		return result;
	}

	/**
	 * 
	 * <p>
	 * Title: mapListToListList
	 * </p>
	 * <p>
	 * Descriptions: 将mapList转listList,带排序
	 * </p>
	 * <p>
	 * Company:星海
	 * </p>
	 * 
	 * @author 陈刚
	 * @date 2017-5-22 下午02:31:58
	 * @param mapList
	 * @param sortList
	 * @return
	 */
	public static List<List<String>> mapListToListList(List<Map<String, Object>> mapList, List<String> sortList) {
		List<List<String>> result = new ArrayList<List<String>>();
		for (int i = 0; i < mapList.size(); i++) {
			Map<String, Object> map = mapList.get(i);
			List<String> list = new ArrayList<String>();
			for (int j = 0; j < sortList.size(); j++) {
				list.add(map.get(sortList.get(j)) == null ? "" : map.get(sortList.get(j)).toString());
			}
			result.add(list);
		}
		return result;
	}

	public static List<List<String>> mapStringListToListList(List<Map<String, String>> mapList, List<String> sortList) {
		List<List<String>> result = new ArrayList<List<String>>();
		for (int i = 0; i < mapList.size(); i++) {
			Map<String, String> map = mapList.get(i);
			List<String> list = new ArrayList<String>();
			for (int j = 0; j < sortList.size(); j++) {
				list.add(map.get(sortList.get(j)) == null ? "" : map.get(sortList.get(j)).toString());
			}
			result.add(list);
		}
		return result;
	}

	/**
	 * <p>
	 * Title: listListToMapList
	 * </p>
	 * <p>
	 * Descriptions: 将listList转为mapList，键值为第1行的值，（有第0行）
	 * </p>
	 * <p>
	 * Company:星海
	 * </p>
	 * 
	 * @author 陈刚
	 * @date 2017-4-20 上午10:04:02
	 * @return
	 */

	public static List<Map<String, Object>> listListToMapList(List<List<String>> listList) {
		List<Map<String, Object>> mapList = new ArrayList<Map<String, Object>>();
		for (int i = 0; i < listList.size(); i++) {
			List<String> list = listList.get(i);
			List<String> list2 = listList.get(1);
			Map<String, Object> map = new HashMap<String, Object>();
			for (int j = 0; j < list2.size(); j++) {
				map.put(list2.get(j), list.get(j));
			}
			mapList.add(map);
		}
		return mapList;
	}

	@SuppressWarnings("unused")
	public static HSSFWorkbook writeXlsTest(List<String> list) {
		HSSFWorkbook wb = new HSSFWorkbook();
		Map<String, List<Integer>> styleMap = new HashMap<String, List<Integer>>();
		styleMap.put(style_style, Arrays.asList(style_alignment_center, style_vertical_center));
		styleMap.put(style_font, Arrays.asList(font_bold));
		CellStyle cellStyle = getCellStyle(wb, styleMap);// wb.createCellStyle();
		// cellStyle.setAlignment(CellStyle.ALIGN_CENTER);
		try {
			// 创建HSSFWorkbook对象

			// 创建HSSFSheet对象
			HSSFSheet sheet = wb.createSheet("sheet0");
			HSSFRow row = sheet.createRow(0);
			HSSFRow row2 = sheet.createRow(1);
			HSSFCell cell0 = row.createCell(0);
			// HSSFCell cell1 = row.createCell(1);
			cell0.setCellValue("张三");
			cell0.setCellStyle(cellStyle);
			HSSFRow rows = sheet.createRow(0);
			HSSFCell cells = rows.createCell(5);
			cells.setCellValue("list");
			HSSFRow row孙三 = sheet.createRow(0);
			System.out.println("----------");
			System.out.println(sheet.getRow(0));
			;
			System.out.println(sheet.getRow(1));
			;
			System.out.println(sheet.getRow(2));
			;
			// cell1.setCellValue("asdf");
			// HSSFCell cell2 = row2.createCell(0);
			CellRangeAddress cellRangeAddress = new CellRangeAddress(0, 1, 0, 1);
			sheet.addMergedRegion(cellRangeAddress);
		} catch (Exception e) {
			e.printStackTrace();
		}

		return wb;

	}

	/**
	 * 
	 * <p>
	 * Title:getCellStyle
	 * </p>
	 * <p>
	 * Descriptions:
	 * </p>
	 * 
	 * @author 陈刚
	 * @date 2017-8-25 下午02:33:51
	 * @param
	 * @return
	 */
	@SuppressWarnings("deprecation")
	public static CellStyle getCellStyle(HSSFWorkbook wb, Map<String, List<Integer>> style) {
		CellStyle cellStyle = wb.createCellStyle();
		HSSFFont font = wb.createFont();
		for (Entry<String, List<Integer>> entry : style.entrySet()) {
			if (style_style.equalsIgnoreCase(entry.getKey())) {
				List<Integer> list = entry.getValue();
				for (Integer integer : list) {
					switch (integer) {
					case style_alignment_center:
						cellStyle.setAlignment(CellStyle.ALIGN_CENTER);
						break;
					case style_vertical_center:
						cellStyle.setVerticalAlignment(CellStyle.VERTICAL_CENTER);
						break;

					default:
						break;
					}
				}
			} else if (style_font.equalsIgnoreCase(entry.getKey())) {
				List<Integer> list = entry.getValue();
				for (Integer integer : list) {
					switch (integer) {
					case font_bold:
						if (font == null) {
							font = wb.createFont();
						}
						font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
						cellStyle.setFont(font);
						break;

					default:
						break;
					}
				}
			}
		}

		/*
		 * cellStyle.setAlignment(CellStyle.ALIGN_CENTER);
		 * cellStyle.setVerticalAlignment(cellStyle.VERTICAL_CENTER); HSSFFont font =
		 * wb.createFont(); font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
		 * cellStyle.setFont(font);
		 */
		return cellStyle;
	}

	/**
	 * 
	 * <p>
	 * Title:getCellStyleCenter
	 * </p>
	 * <p>
	 * Descriptions: 得到水平、垂直居中样式
	 * </p>
	 * 
	 * @author 陈刚
	 * @date 2017-8-25 下午02:37:13
	 * @param
	 * @return
	 */
	@SuppressWarnings("deprecation")
	public static CellStyle getCellStyleCenter(HSSFWorkbook wb) {
		CellStyle cellStyle = wb.createCellStyle();
		cellStyle.setAlignment(CellStyle.ALIGN_CENTER);
		cellStyle.setVerticalAlignment(CellStyle.VERTICAL_CENTER);
		return cellStyle;
	}

	/**
	 * 
	 * <p>
	 * Title:getCellStyleCenter
	 * </p>
	 * <p>
	 * Descriptions: 得到水平、垂直居中,字体加粗样式
	 * </p>
	 * 
	 * @author 陈刚
	 * @date 2017-8-25 下午02:37:13
	 * @param
	 * @return
	 */
	@SuppressWarnings({ "deprecation" })
	public static CellStyle getCellStyleCenterAndBold(HSSFWorkbook wb) {
		CellStyle cellStyle = wb.createCellStyle();
		cellStyle.setAlignment(CellStyle.ALIGN_CENTER);
		cellStyle.setVerticalAlignment(CellStyle.VERTICAL_CENTER);
		HSSFFont font = wb.createFont();
		font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
		cellStyle.setFont(font);
		return cellStyle;
	}

	/**
	 * <p>
	 * setCellStyleBackgroundColor:()
	 * </p>
	 * 
	 * @description 设置背景色
	 * @author 陈刚
	 * @date 2019年1月24日 下午6:52:35
	 * @param cellStyle
	 * @param color
	 * @return
	 */
	public static CellStyle setCellStyleBackgroundColor(CellStyle cellStyle, short color) {
		cellStyle.setFillForegroundColor(color);
		return cellStyle;
	}

	/**
	 * <p>
	 * setCellStyleBorder:()
	 * </p>
	 * 
	 * @description
	 * @author 陈刚
	 * @date 2019年1月24日 下午6:57:20
	 * @param cellStyle
	 * @return
	 */
	@SuppressWarnings("deprecation")
	public static CellStyle setCellStyleBorder(CellStyle cellStyle) {
		cellStyle.setBorderTop(HSSFCellStyle.BORDER_THIN);
		cellStyle.setBorderBottom(HSSFCellStyle.BORDER_THIN);
		cellStyle.setBorderRight(HSSFCellStyle.BORDER_THIN);
		cellStyle.setBorderLeft(HSSFCellStyle.BORDER_THIN);
		return cellStyle;
	}

	/**
	 * <p>
	 * setCellStyleAlignment:()
	 * </p>
	 * 
	 * @description 设置水平居中
	 * @author 陈刚
	 * @date 2019年1月24日 下午6:58:18
	 */
	@SuppressWarnings("deprecation")
	public static CellStyle setCellStyleAlignment(CellStyle cellStyle) {
		cellStyle.setAlignment(CellStyle.ALIGN_CENTER);
		cellStyle.setVerticalAlignment(CellStyle.VERTICAL_CENTER);
		return cellStyle;
	}

	/**
	 * <p>
	 * setCellStyleAlignment:()
	 * </p>
	 * 
	 * @description 设置水平居中
	 * @author 陈刚
	 * @date 2019年1月24日 下午6:58:18
	 */
	@SuppressWarnings("deprecation")
	public static CellStyle setCellStyleHorizontalAlignment(CellStyle cellStyle) {
		cellStyle.setAlignment(CellStyle.ALIGN_CENTER);
		return cellStyle;
	}

	/**
	 * <p>
	 * setCellStyleAlignment:()
	 * </p>
	 * 
	 * @description 设置垂直居中
	 * @author 陈刚
	 * @date 2019年1月24日 下午6:58:18
	 */
	@SuppressWarnings("deprecation")
	public static CellStyle setCellStyleVerticalAlignment(CellStyle cellStyle) {
		cellStyle.setVerticalAlignment(CellStyle.VERTICAL_CENTER);
		return cellStyle;
	}

	/**
	 * <p>
	 * setCellStyleAlignment:()
	 * </p>
	 * 
	 * @description 设置字体加粗
	 * @author 陈刚
	 * @date 2019年1月24日 下午6:58:18
	 */
	@SuppressWarnings("deprecation")
	public static CellStyle setCellStyleBole(CellStyle cellStyle, HSSFFont font) {
		font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
		cellStyle.setFont(font);
		return cellStyle;
	}

	/**
	 * <p>
	 * setAutoWeight:()
	 * </p>
	 * 
	 * @description 设置表格自动宽度
	 * @author 陈刚
	 * @date 2019年1月28日 上午11:41:18
	 * @param sheet
	 */
	public static void setAutoWeight(Sheet sheet, int cols) {
		for (int j = 0; j < cols; j++) {
			sheet.autoSizeColumn(j);
		}
	}

	public static void setSizeColumn(Sheet sheet, int size) {
		for (int colNO = 0; colNO < size; colNO++) {
			int width = sheet.getColumnWidth(colNO) * 2;
			for (int rowNo = 0; rowNo < sheet.getLastRowNum(); rowNo++) {
				HSSFRow currentRow;
				// 当前行未被使用过
				if (sheet.getRow(rowNo) == null) {
					currentRow = (HSSFRow) sheet.createRow(rowNo);
				} else {
					currentRow = (HSSFRow) sheet.getRow(rowNo);
				}

				if (currentRow.getCell(colNO) != null) {
					HSSFCell currentCell = currentRow.getCell(colNO);
					if (currentCell.getCellType() == HSSFCell.CELL_TYPE_STRING) {
						int length = currentCell.getStringCellValue().getBytes().length;
						if (width < 255 * 256) {
							sheet.setColumnWidth(colNO, width < 3000 ? 3000 : width);
						} else {
							sheet.setColumnWidth(colNO, 6000);
						}
					}
				}
			}
		}
	}

	public static <T> HSSFWorkbook objectGrid(List<T> list, Class<T> clz, boolean hasSeq, List<String> nameList)
			throws IllegalAccessException, IllegalArgumentException, InvocationTargetException {
		Map<String, Method> methodTable = new ConcurrentHashMap<String, Method>();
		Stream.of(clz.getDeclaredFields()).filter(field -> field.getAnnotationsByType(ExcelRow.class) != null)
				.filter(field -> nameList.contains(((ExcelRow) field.getAnnotationsByType(ExcelRow.class)[0]).name()))
				.forEach(field -> {
					String name = ((ExcelRow) field.getAnnotationsByType(ExcelRow.class)[0]).name();
					if (nameList.contains(name)) {
						String fname = field.getName();
						fname = "get" + fname.substring(0, 1).toUpperCase() + fname.substring(1);
						try {
							methodTable.put(name, clz.getDeclaredMethod(fname));
						} catch (NoSuchMethodException | SecurityException e) {
							e.printStackTrace();
						}
					}
				});
		if (hasSeq) {
            nameList.add(0, "序号");
        }
		List<List<String>> rows = new ArrayList<List<String>>() {
			{
				add(nameList);
			}
		};
		int seq = 1;
		int s = nameList.size();
		for (T t : list) {
			List<String> row = new ArrayList<>();
			int i = 0;
			if (hasSeq) {
				row.add(String.valueOf(seq++));
				i = 1;
			}
			for (; i < s; i++) {
				String name = nameList.get(i);
				Method getter = methodTable.get(name);
				if (null != getter) {
					Object attr = getter.invoke(t);
					row.add(attr == null ? "" : attr.toString());
				} else {
					row.add("");
				}
			}
			rows.add(row);
		}
		return writeXlsByValueListHasBorder(rows);
	}

	public static <T> HSSFWorkbook objectGrid(List<T> list, Class<T> clz, boolean hasSeq) {
		List<Field> fields = Stream.of(clz.getDeclaredFields())
				.filter(field -> field.getAnnotationsByType(ExcelRow.class).length > 0)
				.sorted(Comparator.comparing(field -> field.getAnnotationsByType(ExcelRow.class)[0].value()))
				.collect(Collectors.toList());

		List<String> nameList = fields.stream().map(field -> field.getAnnotationsByType(ExcelRow.class)[0].name())
				.collect(Collectors.toList());
		List<Method> getters = fields.stream().map(field -> {
			String fname = field.getName();
			fname = "get" + fname.substring(0, 1).toUpperCase() + fname.substring(1);
			try {
				return clz.getDeclaredMethod(fname);
			} catch (NoSuchMethodException | SecurityException e) {
				e.printStackTrace();
			}
			return null;
		}).collect(Collectors.toList());
		if (hasSeq) {
            nameList.add(0, "序号");
        }
		List<List<String>> rows = new ArrayList<List<String>>() {
			{
				add(nameList);
			}
		};
		int seq = 1;
		int cellCount = fields.size();
		for (T t : list) {
			List<String> row = new ArrayList<>();
			if (hasSeq) {
                row.add(String.valueOf(seq++));
            }
			for (int i = 0; i < cellCount; i++) {
				try {
					row.add(getter(getters.get(i), t));
				} catch (IllegalAccessException | IllegalArgumentException | InvocationTargetException e) {
					e.printStackTrace();
					row.add("");
				}
			}
			rows.add(row);
		}
		return writeXlsByValueListHasBorder(rows);
	}

	public static String getter(Method getter, Object obj)
			throws IllegalAccessException, IllegalArgumentException, InvocationTargetException {
		Object get = getter.invoke(obj);
		if (null == get) {
			return "";
		} else if ((get instanceof Integer) || (get instanceof String) || (get instanceof Long)
				|| (get instanceof Character) || (get instanceof Byte) || (get instanceof Short)) {
			return get.toString();
		} else if ((get instanceof Double) || (get instanceof Float)) {
			return String.format("%.2f", get);
		} else {
			return JSONObject.fromObject(get).toString();
		}
	}
}
